package com.zretc.emptraining.daoimpl;

import java.util.Date;
import java.util.List;

import com.zretc.emptraining.dao.EmpDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.Emp;

public class EmpDaoImpl implements EmpDao {

	@Override
	public boolean addEmp(Emp emp) {
		// 添加员工
		if (emp == null) {
			return false;
		}
		String sql = "insert into emp values(?,?,?,?,?,?,now(),?,?,?,?,?)";
		// 时间为当前系统时间值
		return DBTools.getUpdate(sql, emp.getEmpNo(), emp.getEmpName(),emp.getDeptNo(), emp.getEmpXl(), emp.getEmpSex(),
				emp.getEmpID(), emp.getEmpAddress(), emp.getEmpTel(),emp.getEmpEmail(), emp.getEmpJob() , emp.getEmpPic()) > 0;
	}

	@Override
	public List<Emp> getEmp() {
		String sql = "select * from emp";
		return (List<Emp>) DBTools.exQuery(sql, Emp.class, null);
	}
	
	@Override
	public List<Emp> getEmpByEmpno(int empNo) {
		// 员工编号精确查找
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo AND emp.empNo = ?";
		return  (List<Emp>) DBTools.exQuery(sql, Emp.class, empNo);

	}

	@Override
	public boolean updEmp(int empNo,String empName, int deptNo, String empXl, String empSex, String empID ,
			Date empTime, String empAddress,String empTel, String empEmail, String empJob) {
		// 通过编号修改员工信息（不改照片）
		String sql = "UPDATE emp SET empName = ?,deptNo = ?,empXl = ?,empSex = ?,empID = ?,empTime = ? ,empAddress = ?,empTel = ?,empEmail = ?,empJob = ? WHERE empNo = ?";
		return DBTools.getUpdate(sql, empName , deptNo, empXl, empSex, empID , empTime , empAddress, empTel, empEmail, empJob, empNo) > 0;
	}

	@Override
	public boolean updEmpPic(int empNo, String empPic) {
		// 通过编号修改员工照片
		String sql = "Update emp set empPic = ? where empNo = ?";
		return DBTools.getUpdate(sql, empPic , empNo) > 0 ;
	}
		
	@Override
	public boolean delEmp(int empNo) {
		// 通过编号删除员工
		String sql = "delete from emp where empNo = ?";
		return DBTools.getUpdate(sql, empNo) > 0;
	}
	/**
	 * 分页查询
	 */
	@Override
	public PageData<Emp> getEmpByPage(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE empName LIKE ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, "%" + keywords + "%");
		return pd;
	}
	@Override
	public PageData<Emp> getEmpByPageDeptName(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE deptName LIKE ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, "%" + keywords + "%");
		return pd;
	}
	@Override
	public PageData<Emp> getEmpByPageEmpJob(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE empJob LIKE ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, "%" + keywords + "%");
		return pd;
	}
	@Override
	public PageData<Emp> getEmpByPageEmpNo(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE emp.empNo = ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, keywords);
		return pd;
	}
	@Override
	public PageData<Emp> getEmpByPageEmpSex(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE empSex = ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, keywords);
		return pd;
	}
	@Override
	public PageData<Emp> getEmpByPageEmpXl(int page, int pageSize, String keywords) {
		String sql = "SELECT emp.empNo,emp.empName,emp.deptNo,dept.deptName,emp.empXl,emp.empSex,\r\n" + 
				"emp.empID,emp.empTime,emp.empAddress,emp.empTel,emp.empEmail,emp.empJob,emp.empPic FROM emp INNER JOIN dept ON emp.deptNo = dept.deptNo WHERE empXl Like ?";
		PageData<Emp> pd = DBTools.exQueryByPage(sql, Emp.class , page, pageSize, "%" + keywords + "%");
		return pd;
	}
	
	@Override
	public List<Emp> getEmpByEmptel(String empTel) {
		String sql = "select empNo from emp where empTel = ?";
		List<Emp> list = (List<Emp>) DBTools.exQuery(sql, Emp.class, empTel);
		return list;
	}

	@Override
	public boolean updEmpDeptJob(int empNo, int deptNo, String empJob) {
		String sql = "update emp set deptNo = ?,empJob = ? where empNo = ?";
		return DBTools.getUpdate(sql, deptNo,empJob,empNo) > 0;
	}
	
	@Override
	public boolean delEmpByName(String empName) {
		String sql = "delete from emp where empName = ?";
		return DBTools.getUpdate(sql, empName) > 0;
	}
	
	@Override
	public List<Emp> getEmpByName(String empName) {
		String sql = "select * from emp where empName = ?";
		return  (List<Emp>) DBTools.exQuery(sql, Emp.class, empName);
	}
	
	@Override
	public List<Emp> getEmps(String keywords) {
		String sql = "select *from emp where empName like ?";
		return (List<Emp>) DBTools.exQuery(sql, Emp.class, '%' + keywords + '%');
	}
	
	@Override
	public Emp getEmpsByNo(int empNo) {
		String sql = "select empJob from emp where empNo = ?";
		List<Emp> list = (List<Emp>) DBTools.exQuery(sql, Emp.class, empNo);
		// 如果查询为空
		if (list.size() == 0) {
			return null;
		}
		return list.get(0);
	}
	
	@Override
	public boolean updEmp(String empName, String empAddress, String empEmail, String empPic) {
		String sql = "UPDATE emp SET empAddress = ?,empEmail = ?,empPic=? WHERE empName = ?";
		return DBTools.getUpdate(sql, empAddress,empEmail, empPic,empName) > 0;
	}
	
	@Override
	public Emp getEmpsByTel(String empTel) {
		String sql = "select * from emp where empTel = ?";
		List<Emp> list = (List<Emp>) DBTools.exQuery(sql, Emp.class, empTel);
		// 如果查询为空
		if (list.size() == 0) {
			return null;
		}
		return list.get(0);
	}
}
